﻿Imports System.Data.OleDb
Public Class frmPhieuNhapKho
    Private ReportType As Integer = 0
    Private IndexCurrentMonth As Integer = 0
    Private bEdit As Boolean
    Private Sub frmPhieuNhapKho_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            DisableControl()
            NamPhieuNhapKho_Load()
            dtpTheoNgay.Enabled = True
            Nam_Load()
            Thang_Load()
            Report_TheoNgay()
            'Phân quyền Form
            Dim aPermission(4) As Boolean
            aPermission = PhanQuyenForm("MenuNhapKho", frmMain.UserId)
            cmdAddNew.Enabled = aPermission(0)
            cmdEdit.Enabled = aPermission(1)
            cmdDelete.Enabled = aPermission(2)
            cmdAproved.Enabled = aPermission(3)
            cmdPrint.Enabled = aPermission(4)
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub NamPhieuNhapKho_Load()
        Dim currentYear As Integer = Now.Year
        cbxNamPhieuNhapKho.Items.Clear()
        Try
            While (currentYear >= 2011)
                cbxNamPhieuNhapKho.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                currentYear = currentYear - 1
            End While
            cbxNamPhieuNhapKho.SelectedIndex = 0
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
#Region "Hàm Thêm, Sửa, Xóa"
    Private Sub cmdAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddNew.Click
        Try
            ShowModalForm(frmThemPhieuNhapKho, False, False)
            Select Case ReportType
                Case 0
                    Report_TheoNgay()
                Case 1
                    Report_TheoThang()
                Case 2
                    Report_TheoNam()
                Case 3
                    Report_TuNgayDenNgay()
            End Select
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub cmdEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdEdit.Click
        Try
            Dim PhieuNhapKhoId As Integer = Integer.Parse(dgvPhieuNhapKho.Rows(dgvPhieuNhapKho.CurrentCell.RowIndex).Cells("PhieuNhapKhoId").Value.ToString)
            'kiem tra tinh trang
            'Dim dataAccess As New DataAccess
            'Dim dbConn As New OleDb.OleDbConnection
            'dataAccess.ConnectDatabase_DBAccess(dbConn)
            'Dim dbCommand As OleDbCommand
            'Dim query As String = "Select TrangThai From PhieuNhapKho Where PhieuNhapKhoId=" & PhieuNhapKhoId
            'dbCommand = New OleDbCommand(query, dbConn)
            'Dim TrangThai As String = dbCommand.ExecuteScalar()
            'dbConn.Close()
            'If TrangThai = "Đã duyệt" Then
            '    MessageBox.Show("Phiếu đã duyệt.Bạn không thể chỉnh sửa thông tin", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
            '    Exit Sub
            'Else
            frmThemPhieuNhapKho.PhieuNhapKhoId = PhieuNhapKhoId
            ShowModalForm(frmThemPhieuNhapKho, False, False)
            frmThemPhieuNhapKho.PhieuNhapKhoId = -1
            Select Case ReportType
                Case 0
                    Report_TheoNgay()
                Case 1
                    Report_TheoThang()
                Case 2
                    Report_TheoNam()
                Case 3
                    Report_TuNgayDenNgay()
            End Select
            'End If

        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub cmdAproved_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAproved.Click
        Try
            Dim PhieuNhapKhoId As Integer = Integer.Parse(dgvPhieuNhapKho.Rows(dgvPhieuNhapKho.CurrentCell.RowIndex).Cells("PhieuNhapKhoId").Value.ToString)
            'kiểm tra tình trạng
            'kiem tra tinh trang
            Dim dataAccess As New DataAccess
            Dim dbConn As New OleDb.OleDbConnection
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim dbCommand As OleDbCommand
            Dim query As String = "Select TrangThai From PhieuNhapKho Where PhieuNhapKhoId=" & PhieuNhapKhoId
            dbCommand = New OleDbCommand(query, dbConn)
            Dim TrangThai As String = dbCommand.ExecuteScalar()
            If TrangThai = "Đã duyệt" Then
                MessageBox.Show("Phiếu đã duyệt.Bạn không thể duyệt được nữa", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                dbConn.Close()
                Exit Sub
            Else
                Dim dlgRes As DialogResult
                dlgRes = MessageBox.Show( _
                      "Bạn muốn duyệt xác nhận phiếu nhập kho này." & vbCrLf & "Chú ý: Sau khi duyệt phiếu này bạn không thể chỉnh sửa thông tin phiếu nữa." & vbCrLf & "Thông tin nhập kho sẽ được cập nhật vào kho.", _
                      "Duyệt phiếu nhập kho", _
                MessageBoxButtons.OKCancel, _
                      MessageBoxIcon.Question)
                If dlgRes = DialogResult.OK Then
                    query = "Update PhieuNhapKho Set TrangThai=N'Đã duyệt' Where PhieuNhapKhoId=" & PhieuNhapKhoId & ";Update ChiTietPhieuNhapKho Set TrangThai=N'Đã duyệt' Where PhieuNhapKhoId=" & PhieuNhapKhoId
                    dbCommand = New OleDbCommand(query, dbConn)
                    dbCommand.ExecuteNonQuery()
                    'nhap kho
                    Dim dsPhieuNhapKho As New DataSet
                    Dim dtPhieuNhapKho As New DataTable
                    query = "Select * from ChiTietPhieuNhapKho Where PhieuNhapKhoId=" & PhieuNhapKhoId
                    Dim adapter As OleDb.OleDbDataAdapter
                    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                    adapter.Fill(dsPhieuNhapKho)
                    dtPhieuNhapKho = dsPhieuNhapKho.Tables(0)
                    Dim SoLuongThuc As Single
                    Dim IDSanPham As Integer
                    For i As Integer = 0 To dtPhieuNhapKho.Rows.Count - 1
                        SoLuongThuc = Single.Parse(dtPhieuNhapKho.Rows(i)("SoLuongThuc"))
                        IDSanPham = Integer.Parse(dtPhieuNhapKho.Rows(i)("IDSanPham"))
                        query = "Update SanPham Set SoLuongTon=SoLuongTon + " & SoLuongThuc & " Where IdSanPham=" & IDSanPham
                        dbCommand = New OleDbCommand(query, dbConn)
                        dbCommand.ExecuteNonQuery()
                    Next
                    dbConn.Close()
                    Select Case ReportType
                        Case 0
                            Report_TheoNgay()
                        Case 1
                            Report_TheoThang()
                        Case 2
                            Report_TheoNam()
                        Case 3
                            Report_TuNgayDenNgay()
                    End Select
                End If
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub rbtTatCa_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTatCa.CheckedChanged
        Try
            Select Case ReportType
                Case 0
                    Report_TheoNgay()
                Case 1
                    Report_TheoThang()
                Case 2
                    Report_TheoNam()
                Case 3
                    Report_TuNgayDenNgay()
            End Select
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub rbtChuaDuyet_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtChuaDuyet.CheckedChanged
        Try
            Select Case ReportType
                Case 0
                    Report_TheoNgay()
                Case 1
                    Report_TheoThang()
                Case 2
                    Report_TheoNam()
                Case 3
                    Report_TuNgayDenNgay()
            End Select
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub rbtDaDuyet_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtDaDuyet.CheckedChanged
        Try
            Select Case ReportType
                Case 0
                    Report_TheoNgay()
                Case 1
                    Report_TheoThang()
                Case 2
                    Report_TheoNam()
                Case 3
                    Report_TuNgayDenNgay()
            End Select
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
        Try
            Dim PhieuNhapKhoId As Integer = Integer.Parse(dgvPhieuNhapKho.Rows(dgvPhieuNhapKho.CurrentCell.RowIndex).Cells("PhieuNhapKhoId").Value.ToString)
            frmXoaPhieuNhapKho.PhieuNhapKhoID = PhieuNhapKhoId
            ShowModalForm(frmXoaPhieuNhapKho, False, False)
            frmXoaPhieuNhapKho.PhieuNhapKhoID = -1
            Select ReportType
                Case 0
                    Report_TheoNgay()
                Case 1
                    Report_TheoThang()
                Case 2
                    Report_TheoNam()
                Case 3
                    Report_TuNgayDenNgay()
            End Select
                ''kiem tra tinh trang
                'Dim dataAccess As New DataAccess
                'Dim dbConn As New OleDb.OleDbConnection
                'dataAccess.ConnectDatabase_DBAccess(dbConn)
                'Dim dbCommand As OleDbCommand
                'Dim query As String = "Select TrangThai From PhieuNhapKho Where PhieuNhapKhoId=" & PhieuNhapKhoId
                'dbCommand = New OleDbCommand(query, dbConn)
                'Dim TrangThai As String = dbCommand.ExecuteScalar()
                'If TrangThai = "Đã duyệt" Then
                '    MessageBox.Show("Phiếu đã duyệt.Bạn không thể xóa thông tin", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                '    dbConn.Close()
                '    Exit Sub
                'Else
                '    Dim dlgRes As DialogResult
                '    dlgRes = MessageBox.Show( _
                '          "Bạn muốn xóa phiếu nhập kho này", _
                '          "Xóa loại sản phẩm", _
                '    MessageBoxButtons.OKCancel, _
                '          MessageBoxIcon.Question)
                '    If dlgRes = DialogResult.OK Then
                '        query = "Update PhieuNhapKho Set Xoa=1,NgayXoa=N'" & NgayTao() & "',NguoiXoa='" & NguoiTao() & "'  Where PhieuNhapKhoId=" & PhieuNhapKhoId & ";Update ChiTietPhieuNhapKho Set Xoa=1,NgayXoa=N'" & NgayTao() & "',NguoiXoa='" & NguoiTao() & "'  Where PhieuNhapKhoId=" & PhieuNhapKhoId
                '        dbCommand = New OleDbCommand(query, dbConn)
                '        dbCommand.ExecuteNonQuery()
                '        dbConn.Close()
                '        Select Case ReportType
                '            Case 0
                '                Report_TheoNgay()
                '            Case 1
                '                Report_TheoThang()
                '            Case 2
                '                Report_TheoNam()
                '            Case 3
                '                Report_TuNgayDenNgay()
                '        End Select
                '    End If

                'End If
        Catch ex As Exception
            MessageBox.Show("Không có phiếu nào được chọn.Xin vui lòng kiểm tra lại", "Thông báo", MessageBoxButtons.OK)
        End Try
    End Sub
#End Region
#Region "Load Phiếu nhập kho"
    Public Sub Report_TheoNgay()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsPhieuNhapKho As New DataSet
            Dim dtPhieuNhapKho As New DataTable
            Dim query As String = ""
            If rbtTatCa.Checked Then
                query = "Select * from PhieuNhapKho Where Xoa=0 And LoaiPhieu=N'PhieuNhapKho' And NgayNhapKho='" & dtpTheoNgay.Value.ToShortDateString & "' Order By Cast(So As int) DESC"
            ElseIf rbtChuaDuyet.Checked Then
                query = "Select * from PhieuNhapKho Where Xoa=0 And LoaiPhieu=N'PhieuNhapKho' And TrangThai=N'Chưa duyệt' And NgayNhapKho='" & dtpTheoNgay.Value.ToShortDateString & "' Order By Cast(So AS int )DESC"
            ElseIf rbtDaDuyet.Checked Then
                query = "Select * from PhieuNhapKho Where Xoa=0 And LoaiPhieu=N'PhieuNhapKho' And TrangThai=N'Đã duyệt' And NgayNhapKho='" & dtpTheoNgay.Value.ToShortDateString & "' Order By Cast(So As int)DESC"
            End If
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsPhieuNhapKho)
            dbConn.Close()
            dgvPhieuNhapKho.AutoGenerateColumns = False
            dtPhieuNhapKho = dsPhieuNhapKho.Tables(0)
            dtPhieuNhapKho.Columns.Add("STT")
            For i As Integer = 0 To dtPhieuNhapKho.Rows.Count - 1
                dtPhieuNhapKho.Rows(i)("STT") = i + 1
                formatDate2VN(dtPhieuNhapKho.Rows(i)("Ngay"))
                formatDate2VN(dtPhieuNhapKho.Rows(i)("NgayNhapKho"))
            Next
            'filter by customer
            Dim drFilter() As DataRow
            If txtTenKhachHang.Text <> "" Then
                drFilter = dtPhieuNhapKho.Select("KhachHangId='" & lblKhachHangID.Text.Trim & "'")
            Else
                drFilter = dtPhieuNhapKho.Select()
            End If
            'convert datarow to datatable
            Dim dtFilterByCustomer As DataTable = dtPhieuNhapKho.Clone()
            For Each dr As DataRow In drFilter
                dtFilterByCustomer.ImportRow(dr)
            Next
            'filter by code
            Dim dtFilterByProductCode As DataTable = dtFilterByCustomer.Clone()
            If txtTenSanPham.Text <> "" Then
                For Each dr As DataRow In dtFilterByCustomer.Rows
                    'check 
                    If CheckCodeInOrder(dr.Item("PhieuNhapKhoID"), txtTenSanPham.Text.Trim) Then
                        dtFilterByProductCode.ImportRow(dr)
                    End If
                Next
            Else
                dtFilterByProductCode = dtFilterByCustomer
            End If
            dgvPhieuNhapKho.DataSource = dtFilterByProductCode
            If dgvPhieuNhapKho.Rows.Count - 1 > 0 Then
                dgvPhieuNhapKho.Rows(0).Selected = True
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Protected Function CheckCodeInOrder(ByVal OrderID As Integer, ByVal ProductCode As String) As Boolean
        Try
            Dim KetQua As Integer
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Using dbConn
                Dim dbCommand As OleDb.OleDbCommand
                dbCommand = New OleDbCommand("ThongKeKho_CheckCodeInOrder", dbConn)
                dbCommand.CommandType = CommandType.StoredProcedure
                dbCommand.Parameters.Add("@OrderID", OleDbType.Integer).Value = OrderID
                dbCommand.Parameters.Add("@ProductCode", OleDbType.VarWChar).Value = ProductCode
                KetQua = dbCommand.ExecuteScalar()
                dbConn.Close()
            End Using
            If KetQua = 1 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception

        End Try
    End Function
    Public Sub Report_TheoThang()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsPhieuNhapKho As New DataSet
            Dim dtPhieuNhapKho As New DataTable
            Dim query As String = ""
            Dim Thang As MonthInfo
            Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
            If Thang Is Nothing Then
                MessageBox.Show("Bạn chưa chọn tháng.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If
            If rbtTatCa.Checked Then
                query = "Select * from PhieuNhapKho Where Xoa=0 And LoaiPhieu=N'PhieuNhapKho' And ThangNhap='" & Thang.TenThang & "' Order By Cast(So As Int) DESC"
            ElseIf rbtChuaDuyet.Checked Then
                query = "Select * from PhieuNhapKho Where Xoa=0 And LoaiPhieu=N'PhieuNhapKho' And TrangThai=N'Chưa duyệt' And ThangNhap='" & Thang.TenThang & "' Order By Cast(So As Int) DESC"
            ElseIf rbtDaDuyet.Checked Then
                query = "Select * from PhieuNhapKho Where Xoa=0 And LoaiPhieu=N'PhieuNhapKho' And TrangThai=N'Đã duyệt' And ThangNhap='" & Thang.TenThang & "' Order By Cast(So As Int) DESC"
            End If
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsPhieuNhapKho)
            dbConn.Close()
            dgvPhieuNhapKho.AutoGenerateColumns = False
            dtPhieuNhapKho = dsPhieuNhapKho.Tables(0)
            dtPhieuNhapKho.Columns.Add("STT")
            For i As Integer = 0 To dtPhieuNhapKho.Rows.Count - 1
                dtPhieuNhapKho.Rows(i)("STT") = i + 1
                formatDate2VN(dtPhieuNhapKho.Rows(i)("Ngay"))
                formatDate2VN(dtPhieuNhapKho.Rows(i)("NgayNhapKho"))
            Next
            'filter by customer
            Dim drFilter() As DataRow
            If txtTenKhachHang.Text <> "" Then
                drFilter = dtPhieuNhapKho.Select("KhachHangId='" & lblKhachHangID.Text.Trim & "'")
            Else
                drFilter = dtPhieuNhapKho.Select()
            End If
            'convert datarow to datatable
            Dim dtFilterByCustomer As DataTable = dtPhieuNhapKho.Clone()
            For Each dr As DataRow In drFilter
                dtFilterByCustomer.ImportRow(dr)
            Next
            'filter by code
            Dim dtFilterByProductCode As DataTable = dtFilterByCustomer.Clone()
            If txtTenSanPham.Text <> "" Then
                For Each dr As DataRow In dtFilterByCustomer.Rows
                    'check 
                    If CheckCodeInOrder(dr.Item("PhieuNhapKhoID"), txtTenSanPham.Text.Trim) Then
                        dtFilterByProductCode.ImportRow(dr)
                    End If
                Next
            Else
                dtFilterByProductCode = dtFilterByCustomer
            End If

            dgvPhieuNhapKho.DataSource = dtFilterByProductCode
            'filter

            'end filter

            If dgvPhieuNhapKho.Rows.Count - 1 > 0 Then
                dgvPhieuNhapKho.Rows(0).Selected = True
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Public Sub Report_TheoNam()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsPhieuNhapKho As New DataSet
            Dim dtPhieuNhapKho As New DataTable
            Dim query As String = ""
            Dim Nam As YearInfo
            Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
            If Nam Is Nothing Then
                MessageBox.Show("Bạn chưa chọn năm.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If
            If rbtTatCa.Checked Then
                query = "Select * from PhieuNhapKho Where Xoa=0 And LoaiPhieu=N'PhieuNhapKho' And NamNhap='" & Nam.TenNam & "' Order By Cast(So As int) DESC"
            ElseIf rbtChuaDuyet.Checked Then
                query = "Select * from PhieuNhapKho Where Xoa=0 And LoaiPhieu=N'PhieuNhapKho' And TrangThai=N'Chưa duyệt' And NamNhap='" & Nam.TenNam & "' Order By Cast(So As Int) DESC"
            ElseIf rbtDaDuyet.Checked Then
                query = "Select * from PhieuNhapKho Where Xoa=0 And LoaiPhieu=N'PhieuNhapKho' And TrangThai=N'Đã duyệt' And NamNhap='" & Nam.TenNam & "' Order By Cast(So as int) DESC"
            End If
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsPhieuNhapKho)
            dbConn.Close()
            dgvPhieuNhapKho.AutoGenerateColumns = False
            dtPhieuNhapKho = dsPhieuNhapKho.Tables(0)
            dtPhieuNhapKho.Columns.Add("STT")
            For i As Integer = 0 To dtPhieuNhapKho.Rows.Count - 1
                dtPhieuNhapKho.Rows(i)("STT") = i + 1
                formatDate2VN(dtPhieuNhapKho.Rows(i)("Ngay"))
                formatDate2VN(dtPhieuNhapKho.Rows(i)("NgayNhapKho"))
            Next
            'filter by customer
            Dim drFilter() As DataRow
            If txtTenKhachHang.Text <> "" Then
                drFilter = dtPhieuNhapKho.Select("KhachHangId='" & lblKhachHangID.Text.Trim & "'")
            Else
                drFilter = dtPhieuNhapKho.Select()
            End If
            'convert datarow to datatable
            Dim dtFilterByCustomer As DataTable = dtPhieuNhapKho.Clone()
            For Each dr As DataRow In drFilter
                dtFilterByCustomer.ImportRow(dr)
            Next
            'filter by code
            Dim dtFilterByProductCode As DataTable = dtFilterByCustomer.Clone()
            If txtTenSanPham.Text <> "" Then
                For Each dr As DataRow In dtFilterByCustomer.Rows
                    'check 
                    If CheckCodeInOrder(dr.Item("PhieuNhapKhoID"), txtTenSanPham.Text.Trim) Then
                        dtFilterByProductCode.ImportRow(dr)
                    End If
                Next
            Else
                dtFilterByProductCode = dtFilterByCustomer
            End If

            dgvPhieuNhapKho.DataSource = dtFilterByProductCode
            'filter

            'end filter
            If dgvPhieuNhapKho.Rows.Count - 1 > 0 Then
                dgvPhieuNhapKho.Rows(0).Selected = True
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Public Sub Report_TuNgayDenNgay()
        Try
            Dim TuNgay As Integer = ConvertDateToInt(dtpTuNgay.Value.ToString.Split(" ")(0))
            Dim DenNgay As Integer = ConvertDateToInt(dtpDenNgay.Value.ToString.Split(" ")(0))
            If DenNgay < TuNgay Then
                Dim dlgRes As DialogResult
                dlgRes = MessageBox.Show( _
                          "Ngày đến không thể nhỏ hơn từ ngày.Vui lòng nhập lại", _
                          "Thông báo", _
                    MessageBoxButtons.OK, _
                          MessageBoxIcon.Question)
                If dlgRes = Windows.Forms.DialogResult.OK Then
                    Exit Sub
                End If
            End If

            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsPhieuNhapKho As New DataSet
            Dim dtPhieuNhapKho As New DataTable
            Dim query As String = ""
            If rbtTatCa.Checked Then
                query = "Select * from PhieuNhapKho Where Xoa=0 And LoaiPhieu=N'PhieuNhapKho' And NgayNhapBangSo <=" & DenNgay & " And NgayNhapBangSo >=" & TuNgay & " Order By CAST(So AS INT) DESC"
            ElseIf rbtChuaDuyet.Checked Then
                query = "Select * from PhieuNhapKho Where Xoa=0 And LoaiPhieu=N'PhieuNhapKho' And TrangThai=N'Chưa duyệt' And NgayNhapBangSo <=" & DenNgay & " And NgayNhapBangSo >=" & TuNgay & " Order By CAST(So AS INT) DESC"
            ElseIf rbtDaDuyet.Checked Then
                query = "Select * from PhieuNhapKho Where Xoa=0 And LoaiPhieu=N'PhieuNhapKho' And TrangThai=N'Đã duyệt' And NgayNhapBangSo <=" & DenNgay & " And NgayNhapBangSo >=" & TuNgay & " Order By CAST(So AS INT) DESC"
            End If
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsPhieuNhapKho)
            dbConn.Close()
            dgvPhieuNhapKho.AutoGenerateColumns = False
            dtPhieuNhapKho = dsPhieuNhapKho.Tables(0)
            dtPhieuNhapKho.Columns.Add("STT")
            For i As Integer = 0 To dtPhieuNhapKho.Rows.Count - 1
                dtPhieuNhapKho.Rows(i)("STT") = i + 1
                formatDate2VN(dtPhieuNhapKho.Rows(i)("Ngay"))
                formatDate2VN(dtPhieuNhapKho.Rows(i)("NgayNhapKho"))
            Next
            'filter by customer
            Dim drFilter() As DataRow
            If txtTenKhachHang.Text <> "" Then
                drFilter = dtPhieuNhapKho.Select("KhachHangId='" & lblKhachHangID.Text.Trim & "'")
            Else
                drFilter = dtPhieuNhapKho.Select()
            End If
            'convert datarow to datatable
            Dim dtFilterByCustomer As DataTable = dtPhieuNhapKho.Clone()
            For Each dr As DataRow In drFilter
                dtFilterByCustomer.ImportRow(dr)
            Next
            'filter by code
            Dim dtFilterByProductCode As DataTable = dtFilterByCustomer.Clone()
            If txtTenSanPham.Text <> "" Then
                For Each dr As DataRow In dtFilterByCustomer.Rows
                    'check 
                    If CheckCodeInOrder(dr.Item("PhieuNhapKhoID"), txtTenSanPham.Text.Trim) Then
                        dtFilterByProductCode.ImportRow(dr)
                    End If
                Next
            Else
                dtFilterByProductCode = dtFilterByCustomer
            End If
            dgvPhieuNhapKho.DataSource = dtFilterByProductCode
            'filter

            'end filter
            If dgvPhieuNhapKho.Rows.Count - 1 > 0 Then
                dgvPhieuNhapKho.Rows(0).Selected = True
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Public Sub Report_TheoSoPhieu()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsPhieuNhapKho As New DataSet
            Dim dtPhieuNhapKho As New DataTable
            'Them NamPhieuNhapKho
            Dim NamPhieuNhapKho As YearInfo
            NamPhieuNhapKho = CType(cbxNamPhieuNhapKho.SelectedItem, YearInfo)

            Dim query As String = ""
            If rbtTatCa.Checked Then
                query = "Select * from PhieuNhapKho Where Xoa=0 And LoaiPhieu=N'PhieuNhapKho' And So='" & txtSoPhieu.Text.Trim & "' And NamPhieuNhapKho=" & NamPhieuNhapKho.Nam
            ElseIf rbtChuaDuyet.Checked Then
                query = "Select * from PhieuNhapKho Where Xoa=0 And LoaiPhieu=N'PhieuNhapKho' And TrangThai=N'Chưa duyệt' And So='" & txtSoPhieu.Text.Trim & "' And NamPhieuNhapKho=" & NamPhieuNhapKho.Nam
            ElseIf rbtDaDuyet.Checked Then
                query = "Select * from PhieuNhapKho Where Xoa=0 And LoaiPhieu=N'PhieuNhapKho' And TrangThai=N'Đã duyệt' And So='" & txtSoPhieu.Text.Trim & "' And NamPhieuNhapKho=" & NamPhieuNhapKho.Nam
            End If
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsPhieuNhapKho)
            dbConn.Close()
            dgvPhieuNhapKho.AutoGenerateColumns = False
            dtPhieuNhapKho = dsPhieuNhapKho.Tables(0)
            dtPhieuNhapKho.Columns.Add("STT")
            For i As Integer = 0 To dtPhieuNhapKho.Rows.Count - 1
                dtPhieuNhapKho.Rows(i)("STT") = i + 1
                formatDate2VN(dtPhieuNhapKho.Rows(i)("Ngay"))
                formatDate2VN(dtPhieuNhapKho.Rows(i)("NgayNhapKho"))
            Next
            dgvPhieuNhapKho.DataSource = dtPhieuNhapKho
            If dgvPhieuNhapKho.Rows.Count - 1 > 0 Then
                dgvPhieuNhapKho.Rows(0).Selected = True
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
#End Region
#Region "Filter Data"
    Sub DisableControl()
        dtpTheoNgay.Enabled = False
        cbxTheoThang.Enabled = False
        cbxNamTheoThang.Enabled = False
        cbxTheoNam.Enabled = False
        dtpTuNgay.Enabled = False
        dtpDenNgay.Enabled = False
        txtSoPhieu.Enabled = False
        cbxNamPhieuNhapKho.Enabled = False
    End Sub

    Private Sub rbtTheoNgay_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoNgay.CheckedChanged
        DisableControl()
        dtpTheoNgay.Enabled = True
        ReportType = 0
    End Sub
    Private IsPostBack As Boolean = False
    Private Sub rbtTheoThang_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoThang.CheckedChanged
        DisableControl()
        cbxTheoThang.Enabled = True
        cbxNamTheoThang.Enabled = True
        If IsPostBack = False Then
            cbxTheoThang.SelectedIndex = IndexCurrentMonth
        End If
        cbxNamTheoThang.Enabled = True
        If IsPostBack = False Then
            cbxNamTheoThang.SelectedIndex = 0
        End If
        ReportType = 1
        IsPostBack = True
    End Sub
    Private IsPostBack2 As Boolean = False
    Private Sub rbtTheoNam_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoNam.CheckedChanged
        DisableControl()
        cbxTheoNam.Enabled = True
        If IsPostBack2 = False Then
            cbxTheoNam.SelectedIndex = 0
        End If
        ReportType = 2
        IsPostBack2 = True
    End Sub

    Private Sub rbtTuNgay_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTuNgay.CheckedChanged
        DisableControl()
        dtpTuNgay.Enabled = True
        dtpDenNgay.Enabled = True
        ReportType = 3
    End Sub

    Private Sub rbtTheoPhieu_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoPhieu.CheckedChanged
        Try
            DisableControl()
            txtSoPhieu.Enabled = True
            cbxNamPhieuNhapKho.Enabled = True
            ReportType = 4
        Catch ex As Exception

        End Try
    End Sub
#End Region
#Region "Load combo tháng và combo năm"
#Region "Sub Class"
    Private Class YearInfo
        Dim _Nam As Integer
        Dim _TenNam As String
        Public Sub New(ByVal Nam As Integer, ByVal TenNam As String)
            _Nam = Nam
            _TenNam = TenNam
        End Sub
        Public Function Nam() As Integer
            Return _Nam
        End Function
        Public Function TenNam() As String
            Return _TenNam
        End Function
        Public Overrides Function ToString() As String
            Return _TenNam
        End Function
    End Class
    Private Class MonthInfo
        Dim _Thang As Integer
        Dim _TenThang As String
        Public Sub New(ByVal Thang As Integer, ByVal TenThang As String)
            _Thang = Thang
            _TenThang = TenThang
        End Sub
        Public Function Thang() As Integer
            Return _Thang
        End Function
        Public Function TenThang() As String
            Return _TenThang
        End Function
        Public Overrides Function ToString() As String
            Return _TenThang
        End Function
    End Class
#End Region
#Region "Load data"
    Private Sub Nam_Load()
        Dim currentYear As Integer = Now.Year
        cbxTheoNam.Items.Clear()
        Try
            While (currentYear >= 2011)
                cbxTheoNam.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                cbxNamTheoThang.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                currentYear = currentYear - 1
            End While
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Private Sub Thang_Load()
        Dim currentYear As Integer = 1
        cbxTheoThang.Items.Clear()
        Try
            Dim i As Integer = 0
            While (currentYear <= 12)
                cbxTheoThang.Items.Add(New MonthInfo(currentYear, currentYear.ToString))
                If currentYear = Now.Month Then
                    IndexCurrentMonth = i
                End If
                currentYear = currentYear + 1
                i = i + 1
            End While
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
#End Region
#End Region
    Private Sub cmdThongKe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdThongKe.Click
        Try
            Select Case ReportType
                Case 0
                    Report_TheoNgay()
                Case 1
                    Report_TheoThang()
                Case 2
                    Report_TheoNam()
                Case 3
                    Report_TuNgayDenNgay()
                Case 4
                    Report_TheoSoPhieu()
            End Select
        Catch ex As Exception

        End Try
    End Sub


    Private Sub cmdPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrint.Click
        Try
            Dim PhieuNhapKhoId As Integer = Integer.Parse(dgvPhieuNhapKho.Rows(dgvPhieuNhapKho.CurrentCell.RowIndex).Cells("PhieuNhapKhoId").Value.ToString)
            frmInPhieuNhapKho.PhieuNhapKhoId = PhieuNhapKhoId
            frmInPhieuNhapKho.Show()
        Catch ex As Exception

        End Try
    End Sub
#Region "Tìm kiếm sản phẩm"
    Private Sub SanPham_Search(ByVal KeyWord As String)
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsSanPham As New DataSet
            Dim dtSanPham As New DataTable
            Dim query As String = "SELECT * FROM (Select SanPham.DVTSanPhamId,IDSanPham,MaLoai,TenLoai,MaSanPham,TenSanPham,TenDonViTinh,DonViTinh.TenDonVi,SanPham.GhiChu,SanPham.Xoa From SanPham " & _
                                    " Left Outer Join LoaiSanPham On SanPham.IdLoai=LoaiSanPham.IdLoai " & _
                                    " Left Outer Join DVTSanPham On SanPham.DVTSanPhamId=DVTSanPham.DVTSanPhamId" & _
                                    " Left Outer Join DonViTinh On DonViTinh.IdDonVi=DVTSanPham.IDDonViTinh" & _
                                    " Where SanPham.Xoa=0 And SanPham.MaSanPham Like N'" & KeyWord & "%') AS SP WHERE SP.DVTSANPHAMID IS NOT NULL Order By TenSanPham"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsSanPham)
            dbConn.Close()
            dgvSanPham_Search.AutoGenerateColumns = False
            dtSanPham = dsSanPham.Tables(0)
            dtSanPham.Columns.Add("STT")
            For i As Integer = 0 To dtSanPham.Rows.Count - 1
                dtSanPham.Rows(i)("STT") = i + 1
                'chú giải
            Next

            dgvSanPham_Search.DataSource = dtSanPham
            If dgvSanPham_Search.Rows.Count - 1 > 0 Then
                dgvSanPham_Search.Rows(0).Selected = True
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub txtTenSanPham_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenSanPham.KeyDown
        Try
            If e.KeyCode = Keys.Enter Then
                Dim TenSanPham As String
                Dim DVT As String
                TenSanPham = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("MaSanPham").Value.ToString
                DVT = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("TenDonViTinh").Value.ToString
                txtTenSanPham.Text = TenSanPham
                'hide search panel
                PanelSanPham_Search.Visible = False
                cmdThongKe.Focus()
            End If
        Catch ex As Exception

        End Try
    End Sub



    Private Sub txtTenSanPham_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenSanPham.KeyUp
        Try
            If txtTenSanPham.Text <> "" Then
                If e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then
                    dgvSanPham_Search.Focus()
                End If
            End If
            txtTenSanPham.BackColor = Color.White
        Catch ex As Exception

        End Try
    End Sub
    Private Sub dgvSanPham_Search_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgvSanPham_Search.KeyDown
        Try
            Select Case e.KeyCode
                Case Keys.Down
                    PanelSanPham_Search.Visible = True
                Case Keys.Up
                    PanelSanPham_Search.Visible = True
                Case Keys.Enter
                    Dim TenSanPham As String
                    Dim DVT As String
                    TenSanPham = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("MaSanPham").Value.ToString
                    DVT = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("TenDonViTinh").Value.ToString
                    txtTenSanPham.Text = TenSanPham
                    'hide search panel
                    PanelSanPham_Search.Visible = False
                    cmdThongKe.Focus()
                Case Else
                    txtTenSanPham.Focus()
            End Select
        Catch ex As Exception

        End Try
    End Sub

    Private Sub txtTenSanPham_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtTenSanPham.TextChanged
        Try
            If txtTenSanPham.Text <> "" Then
                'show search panel
                If Not bEdit Then
                    PanelSanPham_Search.Visible = True
                    SanPham_Search(txtTenSanPham.Text)
                Else
                    PanelSanPham_Search.Visible = False
                End If
            Else
                'hide search panel
                PanelSanPham_Search.Visible = False
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub dgvSanPham_Search_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvSanPham_Search.CellDoubleClick
        Try
            'Gan Du Lieu
            Dim TenSanPham As String
            Dim DVT As String
            TenSanPham = dgvSanPham_Search.Rows(e.RowIndex).Cells("MaSanPham").Value.ToString
            DVT = dgvSanPham_Search.Rows(e.RowIndex).Cells("TenDonViTinh").Value.ToString
            txtTenSanPham.Text = TenSanPham           
            'hide search panel
            PanelSanPham_Search.Visible = False
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
#End Region
#Region "Tìm kiếm khách hàng"
    Private Sub txtTenKhachHang_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenKhachHang.KeyDown
        Try
            If e.KeyCode = Keys.Enter Then
                lblKhachHangId.Text = dgv_KhachHang_QuickSearch.Rows(dgv_KhachHang_QuickSearch.CurrentCell.RowIndex).Cells("KhachHangId_Search").Value.ToString()
                '---------Lấy Thống Tin Khách Hàng----------
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dsTinhThanh As New DataSet
                Dim dtTinhThanh As New DataTable
                Dim query As String = "Select CodeKhachHang,TenKhachHang From DanhMucKhachHang Where KhachHangId=" & lblKhachHangId.Text
                Dim adapter As OleDb.OleDbDataAdapter
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dsTinhThanh)

                dtTinhThanh = dsTinhThanh.Tables(0)
                '
                txtTenKhachHang.Text = IIf(IsDBNull(dtTinhThanh.Rows(0)("TenKhachHang")), "", dtTinhThanh.Rows(0)("TenKhachHang"))
                Panel_KhachHang.Visible = False
                dbConn.Close()
                cmdThongKe.Focus()
            ElseIf e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then

            End If
        Catch ex As Exception

        End Try
    End Sub
    Private Sub txtTenKhachHang_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenKhachHang.KeyUp
        Try
            If txtTenKhachHang.Text <> "" Then
                KhachHang_Quick_Search()
                If e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then
                    dgv_KhachHang_QuickSearch.Focus()
                    Panel_KhachHang.Visible = True
                End If
            Else
                Panel_KhachHang.Visible = False
                lblKhachHangID.Text = "0"
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
    Private Sub txtTenKhachHang_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtTenKhachHang.Leave
        Try
            Panel_KhachHang.Visible = False
        Catch ex As Exception

        End Try
    End Sub
    Sub KhachHang_Quick_Search()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsKhachHang As New DataSet
            Dim dtKhachHang As New DataTable
            Dim query As String = "Select KhachHangId,MaKhachHang,TenKhachHang,SoDienThoai,DiaChi From DanhMucKhachHang Where TenKhachHang Like N'%" & txtTenKhachHang.Text.Trim & "%'"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsKhachHang)
            dbConn.Close()
            dtKhachHang = dsKhachHang.Tables(0)
            Panel_KhachHang.Visible = True
            dgv_KhachHang_QuickSearch.AutoGenerateColumns = False
            dgv_KhachHang_QuickSearch.DataSource = dtKhachHang
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
    Private Sub dgv_KhachHang_QuickSearch_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgv_KhachHang_QuickSearch.KeyDown
        Try
            Select Case e.KeyCode
                Case Keys.Down
                    Panel_KhachHang.Visible = True
                Case Keys.Up
                    Panel_KhachHang.Visible = True
                Case Keys.Enter
                    lblKhachHangID.Text = dgv_KhachHang_QuickSearch.Rows(dgv_KhachHang_QuickSearch.CurrentCell.RowIndex).Cells("KhachHangId_Search").Value.ToString()
                    '---------Lấy Thống Tin Khách Hàng----------
                    Dim dbConn As New OleDb.OleDbConnection
                    Dim dataAccess As New DataAccess
                    Dim dsTinhThanh As New DataSet
                    Dim dtTinhThanh As New DataTable
                    Dim query As String = "Select CodeKhachHang,TenKhachHang From DanhMucKhachHang Where KhachHangId=" & lblKhachHangID.Text
                    Dim adapter As OleDb.OleDbDataAdapter
                    dataAccess.ConnectDatabase_DBAccess(dbConn)
                    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                    adapter.Fill(dsTinhThanh)
                    dtTinhThanh = dsTinhThanh.Tables(0)
                    '
                    txtTenKhachHang.Text = IIf(IsDBNull(dtTinhThanh.Rows(0)("TenKhachHang")), "", dtTinhThanh.Rows(0)("TenKhachHang"))
                    '
                    Panel_KhachHang.Visible = False
                    dbConn.Close()
                Case Else
                    txtTenKhachHang.Focus()
            End Select
        Catch ex As Exception

        End Try
    End Sub
#End Region

    Private Sub cmdXemPhieu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdXemPhieu.Click
        Try
            Dim PhieuNhapKhoId As Integer = Integer.Parse(dgvPhieuNhapKho.Rows(dgvPhieuNhapKho.CurrentCell.RowIndex).Cells("PhieuNhapKhoId").Value.ToString)
            frmThemPhieuNhapKho.cmdSave.Enabled = False
            frmThemPhieuNhapKho.PhieuNhapKhoId = PhieuNhapKhoId
            ShowModalForm(frmThemPhieuNhapKho, False, False)
            frmThemPhieuNhapKho.cmdSave.Enabled = True
            frmThemPhieuNhapKho.PhieuNhapKhoId = -1
        Catch ex As Exception

        End Try
        
    End Sub
End Class